import os
from IPython.display import display, HTML
ModuleFolder='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\Py-Databases-SQL-DS\\IBM\\HTMLS\\M01\\'
os.chdir(ModuleFolder)

for file in os.listdir(ModuleFolder):
    #if file.lower().endswith('HTMLs.html'):
        #continue
    if file.lower().endswith('.html'):
        link=file
        #print(link)  
        display(HTML(ModuleFolder+link))
    elif file.lower().endswith('.htm'):
        link=file
        #print(link)  
        display(HTML(ModuleFolder+link))
    else:
        continue

SELECT statement examples

Estimated time: 5 min

Objectives

At the end of this reading, you will learn how to:

  • Use various SELECT queries to retrieve data from the database.

SELECT statement usage

SELECT is classified as a Database Query command used to retrieve information from a database table.

There are various forms in which a SELECT statement is used.

  1. The general syntax of a SELECT statement retrieves the data under the listed columns from Table_1. The code is:
  1. 1
  1. SELECT COLUMN1, COLUMN2, ... FROM TABLE_1 ;
  1. To retrieve all columns from a table, use " * " instead of specifying individual column names. The code below retrieves the entire table.
  1. 1
  1. SELECT * FROM TABLE_1 ;
  1. Use the WHERE clause to filter the required data based on a predicate. The code below filters the response to only the entries that match the predicate.
  1. 1
  1. SELECT <COLUMNS> FROM TABLE_1 WHERE <predicate> ;

SELECT examples

Let's look at these codes in action. Below is a database table called 'COUNTRY,' which contains the columns ID, Name, and CCode. Here, CCode is a 2 letter country code.

ID Name CCode
1 United States of America US
2 China CH
3 Japan JA
4 Germany GE
5 India IN
6 United Kingdom UK
7 France FR
8 Italy IT
9 Canada CA
10 Brazil BR

Example #1

When we apply the SELECT code SELECT * FROM COUNTRY ;, the query retrieves all rows and columns from the database table named COUNTRY.

  • 'SELECT *' instructs the database to select all columns from the table.
  • 'FROM COUNTRY' specifies the table from which to retrieve the data. In this case, it's the "COUNTRY" table, so the entire table appears, as shown below.

Response:

ID Name CCode
1 United States of America US
2 China CH
3 Japan JA
4 Germany GE
5 India IN
6 United Kingdom UK
7 France FR
8 Italy IT
9 Canada CA
10 Brazil BR

Example #2

The SQL query SELECT ID, Name FROM COUNTRY ; retrieves specific columns from a database table named 'COUNTRY'.

  • 'SELECT ID, Name' instructs the database to select two specific columns from the table: "ID" and "Name." It will return these two columns for each row that matches the query criteria.
  • 'FROM COUNTRY' specifies the table from which to retrieve the data, which is the "COUNTRY" table. The table below shows that only the "ID" and "CCode" columns were retrieved.

Response:

ID Name
1 United States of America
2 China
3 Japan
4 Germany
5 India
6 United Kingdom
7 France
8 Italy
9 Canada
10 Brazil

Example #3

The SQL query SELECT * FROM COUNTRY WHERE ID <= 5 ; retrieves all columns from the "COUNTRY" table where the value in the "ID" column is less than or equal to 5.

  • `SELECT * instructs the database to select all columns from the specified table.
  • FROM COUNTRY specifies the table from which to retrieve the data, which is the 'COUNTRY' table.
  • WHERE ID <= 5 ; is a condition that filters the rows from the table. It will only return rows where the value in the "ID" column is less than or equal to 5. In the table below, you can see that only rows 1-5 were retrieved.

Response:

ID Name CCode
1 United States of America US
2 China CH
3 Japan JA
4 Germany GE
5 India IN

Example #4

The SQL querySELECT * FROM COUNTRY WHERE CCode = 'CA' ; retrieves all columns from the "COUNTRY" table where the value in the "CCode" column is equal to 'CA'.

  • `SELECT * instructs the database to select all columns from the specified table.
  • FROM COUNTRY specifies the bale from which to retrieve the data, which is the 'COUNTRY' table.
  • WHERE CCode = 'CA'; is a condition that filters the rows from the table. It will only return rows where the value in the "CCode" column is equal to 'CA.' In the table below, you will find that only the CA column was retrieved.

Response:

ID Name CCode
9 Canada CA

In the lab that follows later in the module, you will apply these concepts and practice more SELECT queries hands-on.

In this reading, you learned that:

  • SELECT is a Database Query command that retrieves information from a database table.
  • The SELECT statement has various forms depending on what action you require.
  • The general syntax will retrieve the data under the listed columns from a named table.
  • Use "*" to retrieve all columns from a table without specifying individual column names.
  • Use the WHERE clause to filter the data based on a predicate.

Author(s)

Rav Ahuja
Abhishek Gagneja


SQL Cheat Sheet: Basics



Command Syntax Description Example
SELECT SELECT column1, column2, ... FROM table_name; SELECT statement is used to fetch data from a database. SELECT city FROM placeofinterest;
WHERE SELECT column1, column2, ...FROM table_name WHERE condition; WHERE clause is used to extract only those records that fulfill a specified condition. SELECT * FROM placeofinterest WHERE city = 'Rome' ;
COUNT SELECT COUNT * FROM table_name ; COUNT is a function that takes the name of a column as argument and counts the number of rows when the column is not NULL. SELECT COUNT(country) FROM placeofinterest WHERE country='Canada';
DISTINCT SELECT DISTINCT columnname FROM table_name; DISTINCT function is used to specify that the statement is a query which returns unique values in specified columns. SELECT DISTINCT country FROM placeofinterest WHERE type='historical';
LIMIT SELECT * FROM table_name LIMIT number; LIMIT is a clause to specify the maximum number of rows the result set must have. SELECT * FROM placeofinterest WHERE airport="pearson" LIMIT 5;
INSERT INSERT INTO table_name (column1,column2,column3...) VALUES(value1,value2,value3...); INSERT is used to insert new rows in the table. INSERT INTO placeofinterest (name,type,city,country,airport) VALUES('Niagara Waterfalls','Nature','Toronto','Canada','Pearson');
UPDATE UPDATE table_name SET[[column1]=[VALUES]] WHERE [condition]; UPDATE used to update the rows in the table. UPDATE placeofinterest SET name = 'Niagara Falls' WHERE name = "Niagara Waterfalls";
DELETE DELETE FROM table_name WHERE [condition]; DELETE statement is used to remove rows from the table which are specified in the WHERE condition. DELETE FROM placeofinterest WHERE city IN ('Rome','Vienna');

Author(s)

Malika Singla


Reading: Examples to ALTER and TRUNCATE tables using MySQL

Estimated time to complete: 5 minutes

In the previous video, the ALTER and TRUNCATE syntax applies to DB2. There are variations in syntax between different databases. This reading will explore some examples of ALTER and TRUNCATE statements using MySQL.

Objective(s)

At the end of this reading, you will be able to:

  • Use the ALTER TABLE statement in the correct syntax.
  • Use TRUNCATE statements in syntax.
  • Execute examples of ALTER and TRUNCATE statements.

ALTER TABLE

ALTER TABLE statements can be used to add or remove columns from a table, to modify the data type of columns, to add or remove keys, and to add or remove constraints. The syntax of the ALTER TABLE statement is:

ADD COLUMN syntax

  1. 1
  2. 2
  1. ALTER TABLE table_name
  2. ADD column_name data_type;

A variation of the syntax for adding column is:

  1. 1
  2. 2
  1. ALTER TABLE table_name
  2. ADD COLUMN column_name data_type;

By default, all the entries are initially assigned the value NULL. You can then use UPDATE statements to add the necessary column values.

For example, to add a telephone_number column to the author table in the library database, the statement will be written as:

  1. 1
  2. 2
  1. ALTER TABLE author 
  2. ADD telephone_number BIGINT;

Here, BIGINT is a data type for Big Integer.
After adding the entries to the new column, a sample output is shown below.

Modify column data type

  1. 1
  2. 2
  1. ALTER TABLE table_name
  2. MODIFY column_name data_type;

Sometimes, the data presented may be in a different format than required. In such a case, we need to modify the data_type of the column. For example, using a numeric data type for telephone_number means you cannot include parentheses, plus signs, or dashes as part of the number. For such entries, the appropriate choice of data_type is CHAR.

To modify the data type, the statement will be written as:

  1. 1
  2. 2
  1. ALTER TABLE author
  2. MODIFY telephone_number CHAR(20);

The entries can then be updated using UPDATE statements. An updated version of the "author" table is shown below.

TRUNCATE Table

TRUNCATE TABLE statements are used to delete all of the rows in a table. The syntax of the statement is:

  1. 1
  1. TRUNCATE TABLE table_name;

So, to truncate the "author" table, the statement will be written as:

  1. 1
  1. TRUNCATE TABLE author;

The output would be as shown in the image below.

Note: The TRUNCATE statement will delete the rows and not the table.

Author

D.M.Naidu

Additional Contributor(s)

Abhishek Gagneja

Reading: Examples to CREATE and DROP tables

Objective(s)

At the end of this lab, you will be able to:

  • Create and Drop tables in the database.

Estimated time to complete: 5 minutes

CREATE TABLE statement

In the previous video, we saw the general syntax to create a table:

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  1. CREATE TABLE TableName (
  2.    COLUMN1 datatype,
  3.    COLUMN2 datatype,
  4.    COLUMN3 datatype, 
  5.    ...
  6. );

Consider the following examples:

  1. Create a TEST table with two columns - ID of type integer and NAME of type varchar. For this, we use the following SQL statement.
  1. 1
  2. 2
  3. 3
  4. 4
  1. CREATE TABLE TEST (
  2.    ID int,
  3.    NAME varchar(30)
  4. );
  1. Create a COUNTRY table with an integer ID column, a two-letter country code column, and a variable length country name column. For this, we may use the following SQL statement.
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  1. CREATE TABLE COUNTRY (
  2.    ID int,
  3.    CCODE char(2),
  4.    Name varchar(60)
  5. );
  1. In the example above, make ID a primary key. Then, the statement will be modified as shown below.
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  1. CREATE TABLE COUNTRY (
  2.    ID int NOT NULL,
  3.    CCODE char(2),
  4.    Name varchar(60)
  5.    PRIMARY KEY (ID)
  6. );

In the above example, the ID column has the NOT NULL constraint added after the datatype, meaning that it cannot contain a NULL or an empty value. This is added since the database does not allow Primary Keys to have NULL values.

DROP TABLE

If the table you are trying to create already exists in the database, you will get an error indicating table XXX.YYY already exists. To circumvent this error, create a table with a different name or first DROP the existing table. It is common to issue a DROP before doing a CREATE in test and development scenarios.

The syntax to drop a table is:

  1. 1
  1. DROP TABLE TableName;

For example, consider that you wish to drop the contents of the table COUNTRY if a table exists in the dataset with the same name. In such a case, the code for the last example becomes

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  1. DROP TABLE COUNTRY;
  2. CREATE TABLE COUNTRY (
  3.    ID int NOT NULL,
  4.    CCODE char(2),
  5.    Name varchar(60)
  6.    PRIMARY KEY (ID)
  7. );

WARNING: Before dropping a table, ensure it doesn't contain important data that can't be recovered easily.

Note that if the table does not exist and you try to drop it, you will see an error like XXX.YYY is an undefined name. You can ignore this error if the subsequent CREATE statement is executed successfully.

In a hands-on lab later in this module, you will practice creating tables and other SQL statements.

Author(s)

Rav Ahuja

Additional Contributor

Abhishek Gagneja

SQL Scripts - Uses and Applications

SQL Scripts

SQL scripts are a series of commands or a program that will be executed on an SQL server.

SQL scripts are useful for making complex database changes and can be used to create, modify, or delete database objects such as tables, views, stored procedures, and functions.

Applications of SQL Scripts

Here are some of the things that you can do with SQL scripts:

  • Create tables
    You can use SQL scripts to create new tables in your database. This is useful when you need to add new functionality to your application or when you want to store new types of data.

  • Drop tables
    SQL scripts often have commands to Drop tables from databases. This is especially important before Create table commands to make sure that a table with the same name doesnt exist in the database already.

  • Insert data
    SQL scripts can also be used to insert data into your tables. This is useful when you need to populate your database with test data or when you want to import data from an external source.

  • Update data
    You can use SQL scripts to update existing data in your tables. This is useful when you need to correct errors or update records based on changing business requirements.

  • Delete data
    SQL scripts can also be used to delete data from your tables. This is useful when you need to remove old or obsolete records from your database.

  • Create views
    Views are virtual tables that allow you to query data from multiple tables as if they were a single table. You can use SQL scripts to create views that simplify complex queries and make it easier to work with your data.

  • Create stored procedures
    Stored procedures are precompiled SQL statements that can be executed on demand. You can use SQL scripts to create stored procedures that encapsulate complex business logic and make it easier to manage your database.

  • Create triggers
    Triggers are special types of stored procedures that are automatically executed in response to certain events, such as an insert, update, or delete operation. You can use SQL scripts to create triggers that enforce business rules and maintain data integrity.

Example: Creating Tables

Let us execute a script containing the CREATE TABLE commands for all the tables in a given dataset, rather than create each table manually by typing the DDL commands in the SQL editor.

Note the following points about these scripts.

  1. SQL scripts are basically a set of SQL commands compiled in a single file.
  2. Each command must be terminated with a delimiter or terminator. Most often, the default delimiter is a semicolon ;.
  3. It is advisable to keep the extension of the file as .sql.
  4. Upon importing this file in the phpMyAdmin interface, the commands in the file are run sequentially.

Consider the following script

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
  34. 34
  35. 35
  36. 36
  37. 37
  38. 38
  39. 39
  40. 40
  41. 41
  42. 42
  43. 43
  44. 44
  45. 45
  46. 46
  47. 47
  48. 48
  49. 49
  50. 50
  51. 51
  52. 52
  1. DROP TABLE IF EXISTS PATIENTS;
  2. DROP TABLE IF EXISTS MEDICAL_HISTORY;
  3. DROP TABLE IF EXISTS MEDICAL_PROCEDURES;
  4. DROP TABLE IF EXISTS MEDICAL_DEPARTMENTS;
  5. DROP TABLE IF EXISTS MEDICAL_LOCATIONS;
  8. CREATE TABLE PATIENTS (
  9.   PATIENT_ID CHAR(9) NOT NULL,
  10.   FIRST_NAME VARCHAR(15) NOT NULL,
  11.   LAST_NAME VARCHAR(15) NOT NULL,
  12.   SSN CHAR(9),
  13.   BIRTH_DATE DATE,
  14.   SEX CHAR,
  15.   ADDRESS VARCHAR(30),
  16.   DEPT_ID CHAR(9) NOT NULL,
  17.   PRIMARY KEY (PATIENT_ID)
  18. );
  20. CREATE TABLE MEDICAL_HISTORY (
  21.   MEDICAL_HISTORY_ID CHAR(9) NOT NULL,
  22.   PATIENT_ID CHAR(9) NOT NULL,
  23.   DIAGNOSIS_DATE DATE,
  24.   DIAGNOSIS_CODE VARCHAR(10),
  25.   MEDICAL_CONDITION VARCHAR(100),
  26.   DEPT_ID CHAR(9),
  27.   PRIMARY KEY (MEDICAL_HISTORY_ID)
  28. );
  30. CREATE TABLE MEDICAL_PROCEDURES (
  31.   PROCEDURE_ID CHAR(9) NOT NULL,
  32.   PROCEDURE_NAME VARCHAR(30),
  33.   PROCEDURE_DATE DATE,
  34.   PATIENT_ID CHAR(9) NOT NULL,
  35.   DEPT_ID CHAR(9),
  36.   PRIMARY KEY (PROCEDURE_ID)
  37. );
  39. CREATE TABLE MEDICAL_DEPARTMENTS (
  40.   DEPT_ID CHAR(9) NOT NULL,
  41.   DEPT_NAME VARCHAR(15),
  42.   MANAGER_ID CHAR(9),
  43.   LOCATION_ID CHAR(9),
  44.   PRIMARY KEY (DEPT_ID)
  45. );
  47. CREATE TABLE MEDICAL_LOCATIONS (
  48.   LOCATION_ID CHAR(9) NOT NULL,
  49.   DEPT_ID CHAR(9) NOT NULL,
  50.   LOCATION_NAME VARCHAR(50),
  51.   PRIMARY KEY (LOCATION_ID, DEPT_ID)
  52. );

This script incorporates commands to first drop any tables with the mentioned names in the database. After that, the script contains commands to create 5 different tables. All these commands are executed sequentially on the interface.

The contents of this file can be saved in a .sql file format and executed on the phpMyAdmin interface. This can be done by first selecting the database, uploading the SQL script in the provided space, and executing it, as shown in the image below.
phpMyAdmin Import screen with CVD and Choose file highlighted and Go button.

Upon successful execution of each statement in sequence, an note appears on the interface as shown in the image below. It is also prudent to note that the tables created are now visible in the tree structure on the left under the selected database.

phpMyAdmin Import screen show executed script with database properties.

You may click any of the tables to see its Table Definition (its list of columns, data types, and so on). The image below displays the structure of the table PATIENTS.

The details for the PATIENTS table in phpMyAdmin screen.

Author(s)

Abhishek Gagneja

SQL Cheat Sheet: CREATE TABLE, ALTER, DROP, TRUNCATE

Command Syntax Description Example
CREATE TABLE MySQL/DB2: CREATE TABLE table_name (col1 datatype optional keyword, col2 datatype optional keyword,col3 datatype optional keyword,..., coln datatype optional keyword) CREATE TABLE statement is to create the table. Each column in the table is specified with its name, data type and an optional keyword which could be PRIMARY KEY, NOT NULL, etc., MySQL/DB2: CREATE TABLE employee ( employee_id char(2) PRIMARY KEY, first_name varchar(30) NOT NULL, mobile int);
ALTER TABLE - ADD COLUMN MySQL/DB2:

Option 1. ALTER TABLE table_name ADD column_name_1 datatype....ADD COLUMN column_name_n datatype;


Option 2. ALTER TABLE table_name ADD COLUMN column_name_1 datatype....ADD COLUMN column_name_n datatype;

ALTER TABLE statement is used to add the columns to a table. MySQL/DB2

Option 1. ALTER TABLE employee ADD income bigint;


Option 2. ALTER TABLE employee ADD COLUMN income bigint;
ALTER TABLE - ALTER COLUMN MySQL: ALTER TABLE table_name MODIFY column_name_1 new_data_type;

DB2: ALTER TABLE table_name ALTER COLUMN column_name_1 SET DATA TYPE datatype;

 MySQL: ALTER TABLE MODIFY MODIFY clause is used with the ALTER TABLE statement to modify the data type of columns.

Db2: ALTER TABLE ALTER COLUMN statement is used to modify the data type of columns.

 MySQL: ALTER TABLE employee MODIFY mobile CHAR(20);

DB2: ALTER TABLE employee ALTER COLUMN mobile SET DATA TYPE CHAR(20);
ALTER TABLE - DROP COLUMN MySQL/DB2: ALTER TABLE table_name DROP COLUMN column_name_1 ; ALTER TABLE DROP COLUMN statement is used to remove columns from a table. MySQL/DB2:

ALTER TABLE employee DROP COLUMN mobile ;
ALTER TABLE - RENAME COLUMN MySQL:ALTER TABLE table_name CHANGE COLUMN current_column_name new_column_name datatype [optional keywords];

DB2: ALTER TABLE table_name RENAME COLUMN current_column_name TO new_column_name;

 MySQL: ALTER TABLE CHANGE COLUMN CHANGE COLUMN clause is used to rename the columns in a table.

DB2: ALTER TABLE RENAME COLUMN statement is used to rename the columns in a table.

 MySQL: ALTER TABLE employee CHANGE COLUMN first_name name VARCHAR(255);

DB2: ALTER TABLE employee RENAME COLUMN first_name TO name;
TRUNCATE TABLE MySQL: TRUNCATE TABLE table_name;

DB2: TRUNCATE TABLE table_name IMMEDIATE;

 MySQL: TRUNCATE TABLE statement is used to delete all of the rows in a table.

Db2: The IMMEDIATE specifies to process the statement immediately and that it cannot be undone.

 MySQL: TRUNCATE TABLE employee;

DB2: TRUNCATE TABLE employee IMMEDIATE ;
DROP TABLE MySQL/DB2DROP TABLE table_name ; Use the DROP TABLE statement to delete a table from a database. If you delete a table that contains data, by default the data will be deleted alongside the table. MySQL/DB2:

DROP TABLE employee ;

Author(s)

Himanshu Birla
Niveditha Pandith TS

SQL Cheat Sheet: Intermediate - LIKE, ORDER BY, GROUP BY

Command Syntax (MySQL/DB2) Description Example (MySQL/DB2)
LIKE SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern; LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Two wildcards often used in conjunction with the LIKE operator are percent sign(%) and underscore sign (_), depending upon the SQL engine being used.

 SELECT f_name , l_name FROM employees WHERE address LIKE '%Elgin,IL%';
This command will output all entries with Elgin,IL in the Address.
BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
This generates all records of employees with salaries between 40000 and 80000.
ORDER BY SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; ORDER BY keyword is used to sort the result-set in ascending or descending order. The default is ascending. In case of multiple columns in ORDER BY, the sorting will be done in the sequence of the appearance of the arguments. SELECT f_name, l_name, dep_id FROM employees ORDER BY dep_id DESC, l_name;
This displays the first name, last name, and department ID of employees, first sorted in descending order of department IDs and then sorted alphabetically as per their last names.
GROUP BY SELECT column_name(s) FROM table_name GROUP BY column_name(s) GROUP BY clause is used in collaboration with the SELECT statement to arrange data with identical values into groups. SELECT dep_id, COUNT(*) FROM employees GROUP BY dep_id;
This returns the department IDs and the number of employees in them, grouped by the department IDs.
HAVING SELECT column_name(s) FROM table_name GROUP BY column_name(s) HAVING condition HAVING clause is used in conjunction with GROUP BY clause in collaboration with the SELECT statement in order to filter the data as per the given condition and then group as per identical values of a specified parameter. SELECT DEP_ID, COUNT(*) AS "NUM_EMPLOYEES", AVG(SALARY) AS "AVG_SALARY" FROM EMPLOYEES GROUP BY DEP_ID HAVING count(*) < 4 ORDER BY AVG_SALARY;

Author(s)

Lakshmi Holla
Abhishek Gagneja


SQL Cheat Sheet: FUNCTIONS and Implicit JOIN

Command Syntax (MySQL/DB2) Description Example (MySQL/DB2)
COUNT SELECT COUNT(column_name) FROM table_name WHERE condition; COUNT function returns the number of rows that match a specified criterion. SELECT COUNT(dep_id) FROM employees;
AVG SELECT AVG(column_name) FROM table_name WHERE condition; AVG function returns the average value of a numeric column. SELECT AVG(salary) FROM employees;
SUM SELECT SUM(column_name) FROM table_name WHERE condition; SUM function returns the total sum of a numeric column. SELECT SUM(salary) FROM employees;
MIN SELECT MIN(column_name) FROM table_name WHERE condition; MIN function returns the smallest value of the SELECTED column. SELECT MIN(salary) FROM employees;
MAX SELECT MAX(column_name) FROM table_name WHERE condition; MAX function returns the largest value of the SELECTED column. SELECT MAX(salary) FROM employees;
ROUND SELECT ROUND(2number, decimals, operation) AS RoundValue; ROUND function rounds a number to a specified number of decimal places. SELECT ROUND(salary) FROM employees;
LENGTH SELECT LENGTH(column_name) FROM table; LENGTH function returns the length of a string (in bytes). SELECT LENGTH(f_name) FROM employees;
UCASE SELECT UCASE(column_name) FROM table; UCASE function displays the column name in each table in uppercase. SELECT UCASE(f_name) FROM employees;
LCASE SELECT LCASE(column_name) FROM table; LCASE function displays the column name in each table in lowercase. SELECT LCASE(f_name) FROM employees;
DISTINCT SELECT DISTINCT column_name FROM table; DISTINCT function is used to display data without duplicates. SELECT DISTINCT UCASE(f_name) FROM employees;
DAY SELECT DAY(column_name) FROM table DAY function returns the day of the month for a given date. SELECT DAY(b_date) FROM employees where emp_id = 'E1002';
CURRENT_DATE SELECT CURRENT_DATE; CURRENT_DATE is used to display the current date. SELECT CURRENT_DATE;
DATEDIFF() SELECT DATEDIFF(date1, date2); DATEDIFF() is used to calculate the difference between two dates or time stamps. The default value generated is the difference in number of days. SELECT DATEDIFF(CURRENT_DATE, date_column) FROM table;
FROM_DAYS() SELECT FROM_DAYS(number_of_days); FROM_DAYS() is used to convert a given number of days to YYYY-MM-DD format. SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, date_column)) FROM table;
DATE_ADD() SELECT DATE_ADD(date, INTERVAL n type); DATE_ADD() is used to calculate the date after lapse of mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days after what is mentioned in date column. The type valiable can also be months or years. SELECT DATE_ADD(date, INTERVAL 3 DAY);;
DATE_SUB() SELECT DATE_SUB(date, INTERVAL n type); DATE_SUB() is used to calculate the date prior to the record date by mentioned number of units of date type, i.e. if n=3 and type=DAY, the result is a date 3 days before what is mentioned in date column. The type valiable can also be months or years. SELECT DATE_SUB(date, INTERVAL 3 DAY);;
Subquery SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE]) Subquery is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

SELECT emp_id, f_name, l_name, salary
FROM employees
where salary
< (SELECT AVG(salary)
FROM employees);

SELECT * FROM ( SELECT emp_id, f_name, l_name, dep_id FROM employees) AS emp4all;


SELECT * FROM employees WHERE job_id IN (SELECT job_ident FROM jobs);
Implicit Inner Join SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name; Implicit Inner Join combines two or more records but displays only matching values in both tables. Inner join applies only the specified columns. SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident;
Implicit Cross Join SELECT column_name(s) FROM table1, table2; Implicit Cross Join is defined as a Cartesian product where the number of rows in the first table is multiplied by the number of rows in the second table. SELECT * FROM employees, jobs;

Author(s)

Lakshmi Holla
Abhishek Gagneja


SQL Cheat Sheet: Accessing Databases using Python

SQLite

Topic Syntax Description Example
connect() sqlite3.connect() Create a new database and open a database connection to allow sqlite3 to work with it. Call sqlite3.connect() to create a connection to the database INSTRUCTOR.db in the current working directory, implicitly creating it if it does not exist. 
  1. 1
  2. 2
  1. import sqlite3
  2. con = sqlite3.connect("INSTRUCTOR.db")
cursor() con.cursor()
To execute SQL statements and fetch results from SQL queries, use a database cursor. Call con.cursor() to create the Cursor. 
  1. 1
  1. cursor_obj = con.cursor()
execute() cursor_obj.execute()
The execute method in Python's SQLite library allows to perform SQL commands, including retrieving data from a table using a query like "Select * from table_name." When you execute this command, the result is obtained as a collection of table data stored in an object, typically in the form of a list of lists. 
  1. 1
  1. cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')''')
fetchall() cursor_obj.fetchall()

The fetchall() method in Python retrieves all the rows from the result set of a query and presents them as a list of tuples.

 
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  1. statement = '''SELECT * FROM INSTRUCTOR'''
  2. cursor_obj.execute(statement)
  3. output_all = cursor_obj.fetchall()
  4. for row_all in output_all:
  5.     print(row_all)
fetchmany() cursor_obj.fetchmany()

The fetchmany() method retrieves the subsequent group of rows from the result set of a query rather than just a single row. To fetch a few rows from the table, use fetchmany(numberofrows) and mention how many rows you want to fetch.

 
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  1. statement = '''SELECT * FROM INSTRUCTOR'''
  2. cursor_obj.execute(statement)
  3. output_many = cursor_obj.fetchmany(2)
  4. for row_many in output_many:
  5.     print(row_many)
read_sql_query() read_sql_query()

read_sql_query() is a function provided by the Pandas library in Python, and it is not specific to MySQL. It is a generic function used for executing SQL queries on various database systems, including MySQL, and retrieving the results as a Pandas DataFrame.

 
  1. 1
  1. df = pd.read_sql_query("select * from instructor;", conn)
shape dataframe.shape
It provides a tuple indicating the shape of a DataFrame or Series, represented as (number of rows, number of columns). 
  1. 1
  1. df.shape
close() con.close()
con.close() is a method used to close the connection to a MySQL database. When called, it terminates the connection, releasing any associated resources and ensuring the connection is no longer active. This is important for managing database connections efficiently and preventing resource leaks in your MySQL database interactions. 
  1. 1
  1. con.close()
CREATE TABLE CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ... );
The CREATE TABLE statement is used to define and create a new table within a database. It specifies the table's name, the structure of its columns (including data types and constraints), and any additional properties such as indexes. This statement essentially sets up the blueprint for organizing and storing data in a structured format within the database. 
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  1. CREATE TABLE INTERNATIONAL_STUDENT_TEST_SCORES ( <br>
  2. country VARCHAR(50),  <br>
  3. first_name VARCHAR(50), <br>
  4. last_name VARCHAR(50),  <br>
  5. test_score INT
  6. );
barplot() seaborn.barplot(x="x-axis_variable", y="y-axis_variable", data=data)
seaborn.barplot() is a function in the Seaborn Python data visualization library used to create a bar plot, also known as a bar chart. It is particularly used to display the relationship between a categorical variable and a numeric variable by showing the average value for each category. 
  1. 1
  2. 2
  1. import seaborn
  2. seaborn.barplot(x='Test_Score',y='Frequency', data=dataframe)
read_csv() df = pd.read_csv('file_path.csv')
read_csv() is a function in Python's Pandas library used for reading data from a Comma-Separated Values (CSV) file and loading it into a Pandas DataFrame. It's a common method for working with tabular data stored in CSV format 
  1. 1
  2. 2
  1. import pandas
  2. df = pandas.read_csv('https://data.cityofchicago.org/resource/jcxq-k9xf.csv')
to_sql() df.to_sql('table_name', index=False)
df.to_sql() is a method in Pandas, a Python data manipulation library used to write the contents of a DataFrame to a SQL database. It allows to take data from a DataFrame and store it structurally within a SQL database table. 
  1. 1
  2. 2
  3. 3
  1. import pandas
  2. df = pandas.read_csv('https://data.cityofchicago.org/resource/jcxq-k9xf.csv')
  3. df.to_sql("chicago_socioeconomic_data", con, if_exists='replace', index=False,method="multi")
read_sql() df = pd.read_sql(sql_query, conn)
read_sql() is a function provided by the Pandas library in Python for executing SQL queries and retrieving the results into a DataFrame from an SQL database. It's a convenient way to integrate SQL database interactions into your data analysis workflows. 
  1. 1
  2. 2
  1. selectQuery = "select * from INSTRUCTOR"
  2. df = pandas.read_sql(selectQuery, conn)

Db2

Topic Syntax Description Example
connect() conn = ibm_db.connect('DATABASE=dbname; HOST=hostname;PORT=port;UID=username; PWD=password;', '', '')
ibm_db.connect() is a Python function provided by the ibm_db library, which is used for establishing a connection to an IBM Db2 or IBM Db2 Warehouse database. It's commonly used in applications that need to interact with IBM Db2 databases from Python. 
  1. 1
  2. 2
  3. 3
  4. 4
  1. import ibm_db
  2. conn = ibm_db.connect('DATABASE=mydb;
  3. HOST=example.com;PORT=50000;UID=myuser;
  4. PWD=mypassword;', '', '')
server_info() ibm_db.server_info()
ibm_db.server_info(conn) is a Python function provided by the ibm_db library, which is used to retrieve information about the IBM Db2 server to which you are connected. 
  1. 1
  2. 2
  3. 3
  4. 4
  1. server = ibm_db.server_info(conn)
  2. print ("DBMS_NAME: ", server.DBMS_NAME)
  3. print ("DBMS_VER:  ", server.DBMS_VER)
  4. print ("DB_NAME:   ", server.DB_NAME)
close() con.close()
con.close() is a method used to close the connection to a db2 database. When called, it terminates the connection, releasing any associated resources and ensuring the connection is no longer active. This is important for managing database connections efficiently and preventing resource leaks in your db2 database interactions. 
  1. 1
  1. con.close()
exec_immediate() sql_statement = "SQL statement goes here"
stmt = ibm_db.exec_immediate(conn, sql_statement)

ibm_db.exec_immediate() is a Python function provided by the ibm_db library, which is used to execute an SQL statement immediately without the need to prepare or bind it. It's commonly used for executing SQL statements that don't require input parameters or don't need to be prepared in advance. 
  1. 1
  2. 2
  3. 3
  1. # Lets first drop the table INSTRUCTOR in case it exists from a previous attempt.
  2. dropQuery = "drop table INSTRUCTOR"
  3. dropStmt = ibm_db.exec_immediate(conn, dropQuery)

Author(s)

Abhishek Gagneja

D.M Naidu

Skills Network Editor
00:00
00:00

SQL Cheat Sheet: Views, Stored Procedures and Transactions

Views

Topic Syntax Description Example
Create View CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
A CREATE VIEW is an alternative way of representing data that exists in one or more tables. CREATE VIEW EMPSALARY AS SELECT EMP_ID, F_NAME, L_NAME, B_DATE, SEX, SALARY FROM EMPLOYEES;
Update a View CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
The CREATE OR REPLACE VIEW command updates a view. CREATE OR REPLACE VIEW EMPSALARY AS SELECT EMP_ID, F_NAME, L_NAME, B_DATE, SEX, JOB_TITLE, MIN_SALARY, MAX_SALARY FROM EMPLOYEES, JOBS WHERE EMPLOYEES.JOB_ID = JOBS.JOB_IDENT;
Drop a View DROP VIEW view_name;
Use the DROP VIEW statement to remove a view from the database. DROP VIEW EMPSALARY;

Stored Procedures in IBM Db2 using SQL

Stored Procedures --#SET TERMINATOR @ CREATE PROCEDURE PROCEDURE_NAME

LANGUAGE

BEGIN

END
@

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

The default terminator for a stored procedure is semicolon(;). To set a different terminator we use SET TERMINATOR clause followed by the terminator such as ‘@’.

 --#SET TERMINATOR @ CREATE PROCEDURE RETRIEVE_ALL

LANGUAGE SQL
READS SQL DATA

DYNAMIC RESULT SETS 1
BEGIN

DECLARE C1 CURSOR
WITH RETURN FOR

SELECT * FROM PETSALE;

OPEN C1;

END
@

Stored Procedures in MySQL using phpMyAdmin

Stored Procedures

DELIMITER //

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

END //

DELIMITER ;


A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

The default terminator for a stored procedure is semicolon (;). To set a different terminator we use DELIMITER clause followed by the terminator such as $$ or //.

 DELIMITER //

CREATE PROCEDURE RETRIEVE_ALL()

BEGIN

SELECT * FROM PETSALE;

END //

DELIMITER ;

Transactions with Db2

Commit command

COMMIT;


 A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

 CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);

INSERT INTO employee( ID, Name, City, Salary, Age) VALUES( 1, ‘Priyanka pal’, ‘Nasik’, 36000, 21), (2, ‘Riya chowdary’, ‘Bangalor’, 82000, 29);

SELECT *FROM employee;
COMMIT;
Rollback command

ROLLBACK;


 A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

As auto-commit is enabled by default, all transactions will be committed. We need to disable this option to see how rollback works.

For db2, we have to disable auto-commit manually. Click the gear icon located on the right side of the SQL Assistant window. Next, select the “On Success” drop-down and choose “commit after the last statement in the script” Remember to save your changes!

disable_auto-commit

INSERT INTO employee VALUES (3, ‘Swetha Tiwari’, ‘Kanpur’, 38000, 38);

SELECT *FROM employee;
ROLLBACK;
SELECT *FROM employee;

Transactions with MySQL

Commit command

COMMIT;


 A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

 CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);

START TRANSACTION;

INSERT INTO employee( ID, Name, City, Salary, Age) VALUES( 1, ‘Priyanka pal’, ‘Nasik’, 36000, 21), (2, ‘Riya chowdary’, ‘Bangalor’, 82000, 29);

SELECT *FROM employee;
COMMIT;
Rollback command

ROLLBACK;


 A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

As auto-commit is enabled by default, all transactions will be committed. We need to disable this option to see how rollback works. For MySQL use the command “SET autocommit = 0;”

INSERT INTO employee VALUES (3, ‘Swetha Tiwari’, ‘Kanpur’, 38000, 38);

SELECT *FROM employee;
ROLLBACK;
SELECT *FROM employee;

Db2 Transactions using Stored Procedure

Commit command

–#SET TERMINATOR @

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

COMMIT;

END
@


 A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

 --#SET TERMINATOR @ CREATE PROCEDURE TRANSACTION_ROSE LANGUAGE SQL MODIFIES SQL DATA

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET retcode = SQLCODE;

UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;

UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;

IF retcode < 0 THEN
ROLLBACK WORK;

ELSE
COMMIT WORK;

END IF;

END
@
Rollback command --#SET TERMINATOR @

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

ROLLBACK;

COMMIT;

END
@


 A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

 --#SET TERMINATOR @ CREATE PROCEDURE TRANSACTION_ROSE LANGUAGE SQL MODIFIES SQL DATA

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE retcode INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET retcode = SQLCODE;

UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;

UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;

IF retcode < 0 THEN
ROLLBACK WORK;

ELSE
COMMIT WORK;

END IF;

END
@

MySQL Transactions using Stored Procedure

Commit command

DELIMITER //

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

COMMIT;

END //

DELIMITER ;


A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

 DELIMITER //

CREATE PROCEDURE TRANSACTION_ROSE()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;
UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;

UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;

COMMIT;

END //

DELIMITER ;
Rollback command

DELIMITER //

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

ROLLBACK;

COMMIT;

END //

DELIMITER ;


A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

 DELIMITER //

CREATE PROCEDURE TRANSACTION_ROSE()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;
UPDATE BankAccounts
SET Balance = Balance-200
WHERE AccountName = ‘Rose’;

UPDATE BankAccounts
SET Balance = Balance-300
WHERE AccountName = ‘Rose’;

COMMIT;

END //

DELIMITER ;

Author(s)

D.M Naidu

SQL Cheat Sheet: JOIN statements



Joins

Topic Syntax Description Example
Cross Join SELECT column_name(s) FROM table1 CROSS JOIN table2;
The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. SELECT DEPT_ID_DEP, LOCT_ID FROM DEPARTMENTS CROSS JOIN LOCATIONS;
Inner Join SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; WHERE condition;
You can use an inner join in a SELECT statement to retrieve only the rows that satisfy the join conditions on every specified table. select E.F_NAME,E.L_NAME, JH.START_DATE from EMPLOYEES as E INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID where E.DEP_ID ='5';
Left Outer Join SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
The LEFT OUTER JOIN will return all records from the left side table and the matching records from the right table. select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;
Right Outer Join SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
The RIGHT OUTER JOIN returns all records from the right table, and the matching records from the left table. select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from EMPLOYEES AS E RIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;
Full Outer Join SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
The FULL OUTER JOIN clause results in the inclusion of rows from two tables. If a value is missing when rows are joined, that value is null in the result table. select E.F_NAME,E.L_NAME,D.DEP_NAME from EMPLOYEES AS E FULL OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;
Self Join SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
A self join is regular join but it can be used to joined with itself. SELECT B.* FROM EMPLOYEES A JOIN EMPLOYEES B ON A.MANAGER_ID = B.MANAGER_ID WHERE A.EMP_ID = 'E1001';

Joins in MySQL using phpMyAdmin

Full Outer Join SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition

UNION

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition

The UNION operator is used to combine the result-set of two or more SELECT statements. select E.F_NAME,E.L_NAME,D.DEP_NAME from EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP

UNION

select E.F_NAME,E.L_NAME,D.DEP_NAME
from EMPLOYEES AS E
RIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP

Author(s)

D.M Naidu

Saving

import os
FromFld='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\Py-Databases-SQL-DS\\IBM\\HTMLS\\'
os.chdir(FromFld)
try:
    !jupyter nbconvert SQL-HTMLs.ipynb --to html --template pj
except Exception as e:
    print('HTML not stored')

import shutil
import os



#file2=Tofld+'P4DSNotes.html'
# The line above copies files from A -> B
#shutil.copy(os.path.join(FromFld,fileh), Tofld)
# The line above copies all the content from A -> B
#shutil.copytree(FromFld, Tofld)
import shutil
FromFld='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\Py-Databases-SQL-DS\\IBM\\HTMLS\\'
Tofld='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\IBM_DS_Jupyter_Tasks\\Python4DataScience\\'
fileh='SQL-HTMLs.html'
filep='SQL-HTMLs.ipynb'
try:
    if os.path.isfile(Tofld+'/'+fileh):
        os.remove(Tofld+'/'+fileh)
        print(fileh, 'deleted in', Tofld)
        shutil.move(os.path.join(FromFld,fileh),os.path.join(Tofld,fileh))
        print(fileh, 'replaced in', Tofld)
    else:
        shutil.move(os.path.join(FromFld,fileh),os.path.join(Tofld,fileh))
        print(fileh, 'written in', Tofld)
except Exception as e:
    print('HTML not moved')

SQL-HTMLs.html deleted in C:\Users\Gamaliel\Documents\G\ADD\IBM_DS\IBM_DS_Jupyter_Tasks\Python4DataScience\
HTML not moved